package com.teamwork;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;

public class TeacherScoreQuery {
  public static void main(String[] args) {
    Scanner scanner = new Scanner(System.in);
    System.out.print("请输入您的ID：");
    int teacherId = scanner.nextInt();
    System.out.print("请输入班级ID：");
    int classId = scanner.nextInt();
    scanner.close();
    TeacherQuery(teacherId,classId);
  }
  public static void TeacherQuery(int teacherId, int classId) {
    try {
        // 加载数据库驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        // 连接数据库
        Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studentexam", "root", "123456");
        // 创建PreparedStatement对象
        String sql = "SELECT AVG(SScore) AS average_score FROM sc WHERE CLId = ?";
        PreparedStatement pstmt = conn.prepareStatement(sql);
        // 设置参数
        pstmt.setInt(1, classId);
        // 执行查询语句
        ResultSet rs = pstmt.executeQuery();
        // 处理查询结果
        if (rs.next()) {
            double averageScore = rs.getDouble("average_score");
            System.out.println("教师ID为" + teacherId + "的班级ID为" + classId + "的平均分为：" + averageScore);
        } else {
            System.out.println("没有找到相关数据");
        }
        // 关闭资源
        rs.close();
        pstmt.close();
        conn.close();
    } catch (Exception e) {
        e.printStackTrace();
    }
}

}
